USE [BMIG_Mensual_POST_CADENA]
GO

/****** Object:  StoredProcedure [dbo].[WASP_M0003_TABLA_TEMPORAL]    Script Date: 11/28/2011 12:18:58 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WASP_M0003_TABLA_TEMPORAL]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WASP_M0003_TABLA_TEMPORAL]
GO

USE [BMIG_Mensual_POST_CADENA]
GO

/****** Object:  StoredProcedure [dbo].[WASP_M0003_TABLA_TEMPORAL]    Script Date: 11/28/2011 12:18:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[WASP_M0003_TABLA_TEMPORAL]
AS

SELECT
	cast(a.AGCOLOC as smallint) as AGCOLOC,
	cast(a.NROCREDITO as numeric(9)) as NROCREDITO,
	MODULO,
	TIPCRED AS TIPO_OPERACION,
	cast(a.FOLIO as numeric(8)) as FOLIO,
	cast(a.MONEDA as varchar(2)) as MONEDA,
	g.fecha_grabacion as  FECGRAB,
	cast(a.FECHCOLOC as varchar(8)) as FECHCOLOC,
	cast(a.MTOBRUTO as numeric(11)) as MTOBRUTO,
--       	cast(a.MTOBRUTO - (a.val_seg +  e.SeguroVida + i.val_seg + c.notario + d.impuesto + case when k.Z07394IMP is not null then k.Z07394IMP else 0 end )as numeric(11)) as MTOLIQ,
--           	f.MTOLIQ MTOLIQ,
              IMPTO = 
	case
		when (a.tipo_moneda = 999 and a.aopap = 2 )then Cast(((d.impuesto /100)*a.aopre)as numeric)
		else d.impuesto
	end,
	DESG = 
	case
		when (a.tipo_moneda = 999 and a.aopap = 2 )then Cast(((j.val_seg /100)*a.aopre)as numeric)
		else j.val_seg
	end,
--	i.val_seg as PROTECC,
	PROTECC=
	case
		when (a.tipo_moneda = 999 and a.aopap = 2)then Cast(((i.val_seg /100)*a.aopre)as numeric)
		else i.val_seg
	end,
--	e.SeguroVida  as VIDA,
	VIDA = 
	case
		when (a.tipo_moneda = 999 and a.aopap = 2)then cast((e.SeguroVida/100)*a.aopre as numeric)
		else e.SeguroVida
	end,
	CESANTIA = 
        case
		when (a.tipo_moneda = 999 and a.aopap = 2)then cast((h.val_seg/100)*a.aopre as numeric)
		else h.val_seg
	end,
	n.val_seg as SALUD,
        cast(c.notario as numeric(9)) as NOTARIA,
	cast(a.tasa as decimal(8,4))  as TASA,
	cast(g.totcuotas  as numeric(3)) as PLAZO,
        CUOTA = 
	case
		when (a.tipo_moneda = 999 and a.aopap = 2)then cast((g.valcuot/100)*a.aopre as numeric)
		else g.valcuot
	end,
	cast(a.RUTDEUD as numeric(8)) as RUTDEUD,
	cast(a.NOMDEUD as varchar(50)) as NOMDEUD,
	cast(a.FECHNAC as varchar(8)) as FECHNAC,
	cast(a.DVDEUD as varchar(1)) as DVDEUD,	
	cast(a.DESIT as varchar(2)) as DESIT,
	cast(a.SEXO as varchar(1)) as SEXO,
	Z07394IMP

INTO #Prim

FROM 
	as_prima_comision_temp a 
LEFT JOIN PrimLiq  f ON a.AGCOLOC = f.aosuc AND a.NROCREDITO = f.aooper AND a.cta = f.aocta
LEFT JOIN prima_comision_notaria c ON a.NROCREDITO = c.ope AND a.AGCOLOC = c.suc AND a.cta = c.cta
LEFT JOIN prima_comision_impuesto d ON a.NROCREDITO = d.ope AND a.AGCOLOC =d.suc AND a.cta = d.cta 
LEFT JOIN prima_comision_seguro_vida_tra  e ON a.NROCREDITO = e.ope AND a.AGCOLOC = e.suc AND a.cta = e.cta 
LEFT JOIN prima_comision_total_cuotas g ON a.NROCREDITO = g.ope AND a.AGCOLOC = g.suc AND a.cta = g.cta
--	seguro_vida_tra h, 
LEFT JOIN prima_comision_seguro_asisten_pen i ON a.NROCREDITO = i.ppoper AND a.AGCOLOC = i.ppsuc AND a.cta = i.ppcta 
LEFT JOIN	prima_comision_seguro_degrav j ON a.NROCREDITO = j.ppoper AND a.AGCOLOC = j.ppsuc AND a.cta = j.ppcta
LEFT JOIN	DBO.TIPO_CREDITO_TEMP K ON A.NROCREDITO = K.oper AND A.AGCOLOC = K.suc AND A.CTA = K.cta
LEFT JOIN	seguro_ces_pri h ON a.NROCREDITO = h.ope AND a.AGCOLOC = h.suc AND a.cta = h.cta
LEFT JOIN	montos_refundidos l ON a.cta = l.aocta AND a.AGCOLOC =  l.Z07391su1 AND a.NROCREDITO = l.Z07391Op1
LEFT JOIN	seguro_salud n ON a.AGCOLOC = n.suc AND a.NROCREDITO = n.ope AND a.cta = n.cta

--WHERE
--	a.AGCOLOC *= f.aosuc and 
--	a.NROCREDITO *= f.aooper and
--	a.cta *= f.aocta and
--	a.NROCREDITO *= c.ope and
-- 	a.AGCOLOC *= c.suc and
--	a.cta *= c.cta and
--	a.NROCREDITO *= d.ope and
--	a.AGCOLOC *=d.suc and
--	a.cta *= d.cta and
--	a.NROCREDITO *= e.ope and
--	a.AGCOLOC *= e.suc and
--	a.cta *= e.cta and
--	a.NROCREDITO *= g.ope and
--	a.AGCOLOC *= g.suc and
--	a.cta *= g.cta and 
--  a.NROCREDITO *= i.ppoper and 
--	a.AGCOLOC *= i.ppsuc and 
--	a.cta *= i.ppcta  and
--	a.NROCREDITO *= j.ppoper and
--  a.AGCOLOC *= j.ppsuc and
--  a.cta *= j.ppcta AND
--	A.NROCREDITO *= K.oper and
--  A.AGCOLOC *= K.suc and
--  A.CTA *= K.cta and
--  a.NROCREDITO *= h.ope and
--  a.AGCOLOC *= h.suc  and
--  a.cta *= h.cta and
--	a.cta *= l.aocta and
--	a.AGCOLOC *=  l.Z07391su1 and
--	a.NROCREDITO *= l.Z07391Op1 and
--	a.AGCOLOC *= n.suc and 
--	a.NROCREDITO *= n.ope  and
--	a.cta *= n.cta


SELECT 
	AGCOLOC,
	NROCREDITO,
	MODULO,
	TIPO_OPERACION,
	FOLIO,
	MONEDA,
	FECGRAB,
	FECHCOLOC,
	MTOBRUTO,
--       	cast(a.MTOBRUTO - (a.val_seg +  e.SeguroVida + i.val_seg + c.notario + d.impuesto + case when k.Z07394IMP is not null then k.Z07394IMP else 0 end )as numeric(11)) as MTOLIQ,
--           	f.MTOLIQ MTOLIQ,
	CAST((Cast(a.MTOBRUTO - (case when a.CESANTIA is not null then a.CESANTIA else 0 end + case when a.DESG is not null then  a.DESG  else 0 end+  case when a.VIDA is not null then a.VIDA  else 0  end+ case when  a.PROTECC  is not null then a.PROTECC else 0 end  + case when  a.NOTARIA  is not null then a.NOTARIA   else 0 end+ case when a.IMPTO is not null then a.IMPTO else 0 end + case when a.Z07394IMP is not null then a.Z07394IMP else 0 end )as numeric(11))- CASE WHEN A.salud IS NOT NULL THEN A.salud ELSE 0 END ) as numeric(11)) as MTOLIQ,
              IMPTO,
	DESG,
--	i.val_seg as PROTECC,
	PROTECC,
--	e.SeguroVida  as VIDA,
	VIDA,
	CESANTIA,
	SALUD,
	NOTARIA,
	TASA,
	PLAZO,
	CUOTA,
	RUTDEUD,
	NOMDEUD,
	FECHNAC,
	DVDEUD,	
	DESIT,
	SEXO

INTO	 [dbo].[prima_temp]

FROM #prim a
GO

